Trying to modify the formula below:
{= SUM(IF(C2:C233<>"",1/COUNTIF(C2:C233,C2:C233)))}
Based on the Values in the column:
H2:H233<>""

How can i put this together?

Try this array formula, confirmed with Ctrl+Shift+Enter:

=SUM(IF(FREQUENCY(IF((C2:C33<>"")*(H2:H33<>""),C2:C33),IF((C2:C33<>"")*(H2:H33<>""),C2:C33))>0,1))

Result = 0

Suppose to be 36

Sorry, I misread the ranges. Replace 33 with 233 throughout the formula, and don't forget to confirm with Ctrl+Shift+Enter.

Is what i did, 233,  Ctrl+Shift+Enter (Mandatory)

Result is 0

Angola	12
Ghana
Ghana
Ghana
Ghana	186
Ghana
Ghana
Ghana	60
Kenya
Kenya	240
Kenya
Kenya	60
Kenya	60
Kenya	36
Mauritius
Morocco	120
Mozambique	90
Mozambique
Mozambique
Mozambique	12
Namibia	36
Nigeria	24
Nigeria	120
Senegal	24
Senegal	24
Senegal	12
Senegal	12
South Africa	15.84
South Africa	90
South Africa	90
South Africa	15.84
South Africa

It shouldn't be 0 even with 33

= SUM(IF(A2:A33<>"",1/COUNTIF(A2:A33,A2:A33)))

Result = 10

Exact Result Should be 9.

• Edited by Eugene Laho 11 hours 16 minutes ago Defined

Where does A2:A33 come from? You originally mentioned C2:C233.

I'm giving the example so you can run it in excel.

Copy Paste, dont forget ctrl + shift + enter :)

• Edited by Eugene Laho 10 hours 25 minutes ago

Ah, I see.

Try this one, again confirmed with Ctrl+Shift+Enter:

=SUM(IF(FREQUENCY(IF(C2:C233<>"",IF(H2:H233<>"",MATCH(C2:C233,C2:C233,0))),ROW(C2:C233)-ROW(C2)+1),1))

(Credits: Aladin Akyurek)

Excel 2010 with free PowerPivot Add-In.
Compatible with Office 2013 Pro Plus.
With DistinctCount().
http://www.mediafire.com/view/s72q5zotzqpmj3x/05_26_15a.xlsx

Hi Hans,

Thank you for time and your solution, works great.

Below is another solution just in case someone will look.

Great Work.

=SUM(IFERROR(NOT(ISBLANK(H2:H233))/COUNTIFS(C2:C233,C2:C233,H2:H233,"<>"&""),0))

• Edited by Eugene Laho 5 hours 31 minutes ago
• Marked as answer by George.Zhao CHNMicrosoft contingent staff, Moderator 5 hours 23 minutes ago